package cn.itcast.web.controller.cargo;

import cn.itcast.service.cargo.ContractProductService;
import cn.itcast.vo.ContractProductVo;
import cn.itcast.web.controller.BaseController;
import com.alibaba.dubbo.config.annotation.Reference;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.CellRangeAddress;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;

import javax.servlet.ServletOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.text.SimpleDateFormat;
import java.util.Date;
import java.util.List;

@Controller
@RequestMapping("/cargo/contract")
public class OutProductController extends BaseController{

    @Reference
    private ContractProductService contractProductService;

    /**
     * 进入出货表导出页面
     */
    @RequestMapping("/print")
    public String print(){
        return "cargo/print/contract-print";
    }

    /**
     * 出货表导出（1）导出
     */
    @RequestMapping("/printExcel_1")
    public void printExcel_1(String inputDate) throws IOException {
        //1、导出第一行
        Workbook workbook = new XSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 合并单元格：开始行0  结束行0  开始列1   结束列8
        sheet.addMergedRegion(new CellRangeAddress(0,0,1,8));
        // 设置单元格宽度
        sheet.setColumnWidth(0,256*6);
        sheet.setColumnWidth(1,256*26);
        sheet.setColumnWidth(2,256*11);
        sheet.setColumnWidth(3,256*29);
        sheet.setColumnWidth(4,256*11);
        sheet.setColumnWidth(5,256*15);
        sheet.setColumnWidth(6,256*10);
        sheet.setColumnWidth(7,256*10);
        sheet.setColumnWidth(8,256*10);

        Row row = sheet.createRow(0);
        // 设置行高
        row.setHeightInPoints(36);
        Cell cell = row.createCell(1);
        // 设置大标题内容：2020年8月份出货表   inputDate = 2020-08
        String bitTitle = inputDate.replace("-0","-").replace("-","年") + "月份出货表";
        cell.setCellValue(bitTitle);
        // 设置第一行第二列的样式
        cell.setCellStyle(this.bigTitle(workbook));


        //2、导出第二行
        String[] titles = {"客户","订单号","货号","数量","工厂","工厂交期","船期","贸易条款"};
        row = sheet.createRow(1);
        row.setHeightInPoints(26);
        for (int i = 0; i < titles.length; i++) {
            cell = row.createCell(i+1);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(this.title(workbook));
        }

        //3、导出数据行
        List<ContractProductVo> list = contractProductService.findByShipTime(inputDate+"%");
        if (list != null && list.size()>0) {
            int start = 2;
            for (ContractProductVo vo : list) {
                row = sheet.createRow(start++);
                row.setHeightInPoints(24);

                // 定义数组存储对象的属性值
                String[] array = {
                        vo.getCustomName(),
                        vo.getContractNo(),
                        vo.getProductNo(),
                        vo.getCnumber()+"",
                        vo.getFactoryName(),
                        parseDate(vo.getDeliveryPeriod()),
                        parseDate(vo.getShipTime()),
                        vo.getTradeTerms()
                };
                for (int i = 0; i < array.length; i++) {
                    cell = row.createCell(i+1);
                    cell.setCellValue(array[i]);
                    cell.setCellStyle(this.text(workbook));
                }

            }
        }

        //4、导出下载
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-disposition","attachment;fileName=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();
    }



    /**
     * 出货表导出（2）模板导出：先读取一个excel模板，再往其中设置数据
     */
    @RequestMapping("/printExcel_2")
    public void printExcel_2(String inputDate) throws IOException {
        // 获取文件流
        InputStream in = session.getServletContext()
                .getResourceAsStream("/make/xlsprint/tOUTPRODUCT.xlsx");

        //1、导出第一行
        Workbook workbook = new XSSFWorkbook(in);
        Sheet sheet = workbook.getSheetAt(0);
        Row row = sheet.getRow(0);
        Cell cell = row.getCell(1);
        String bitTitle = inputDate.replace("-0","-").replace("-","年") + "月份出货表";
        cell.setCellValue(bitTitle);


        //2、获取第三行的样式
        CellStyle[] cellStyles = new CellStyle[8];
        row = sheet.getRow(2);
        for (int i = 0; i < cellStyles.length; i++) {
            cellStyles[i] = row.getCell(i+1).getCellStyle();
        }

        //3、导出数据行
        List<ContractProductVo> list = contractProductService.findByShipTime(inputDate+"%");
        if (list != null && list.size()>0) {
            int start = 2;
            for (ContractProductVo vo : list) {
                row = sheet.createRow(start++);
                row.setHeightInPoints(24);

                // 定义数组存储对象的属性值
                String[] array = {
                        vo.getCustomName(),
                        vo.getContractNo(),
                        vo.getProductNo(),
                        vo.getCnumber()+"",
                        vo.getFactoryName(),
                        parseDate(vo.getDeliveryPeriod()),
                        parseDate(vo.getShipTime()),
                        vo.getTradeTerms()
                };
                for (int i = 0; i < array.length; i++) {
                    cell = row.createCell(i+1);
                    cell.setCellValue(array[i]);
                    cell.setCellStyle(cellStyles[i]);
                }

            }
        }

        //4、导出下载
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-disposition","attachment;fileName=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();
    }



    /**
     * 出货表导出（3）SXSSF 导出百万数据，避免内存溢出
     */
    @RequestMapping("/printExcel")
    public void printExcel(String inputDate) throws IOException {
        //1、导出第一行
        Workbook workbook = new SXSSFWorkbook();
        Sheet sheet = workbook.createSheet();
        // 合并单元格：开始行0  结束行0  开始列1   结束列8
        sheet.addMergedRegion(new CellRangeAddress(0,0,1,8));
        // 设置单元格宽度
        sheet.setColumnWidth(0,256*6);
        sheet.setColumnWidth(1,256*26);
        sheet.setColumnWidth(2,256*11);
        sheet.setColumnWidth(3,256*29);
        sheet.setColumnWidth(4,256*11);
        sheet.setColumnWidth(5,256*15);
        sheet.setColumnWidth(6,256*10);
        sheet.setColumnWidth(7,256*10);
        sheet.setColumnWidth(8,256*10);

        Row row = sheet.createRow(0);
        // 设置行高
        row.setHeightInPoints(36);
        Cell cell = row.createCell(1);
        // 设置大标题内容：2020年8月份出货表   inputDate = 2020-08
        String bitTitle = inputDate.replace("-0","-").replace("-","年") + "月份出货表";
        cell.setCellValue(bitTitle);
        // 设置第一行第二列的样式
        cell.setCellStyle(this.bigTitle(workbook));


        //2、导出第二行
        String[] titles = {"客户","订单号","货号","数量","工厂","工厂交期","船期","贸易条款"};
        row = sheet.createRow(1);
        row.setHeightInPoints(26);
        for (int i = 0; i < titles.length; i++) {
            cell = row.createCell(i+1);
            cell.setCellValue(titles[i]);
            cell.setCellStyle(this.title(workbook));
        }

        //3、导出数据行
        List<ContractProductVo> list = contractProductService.findByShipTime(inputDate+"%");
        if (list != null && list.size()>0) {
            int start = 2;
            for (ContractProductVo vo : list) {
                for (int index = 0; index < 10000; index++) {


                    row = sheet.createRow(start++);
                    row.setHeightInPoints(24);

                    // 定义数组存储对象的属性值
                    String[] array = {
                            vo.getCustomName(),
                            vo.getContractNo(),
                            vo.getProductNo(),
                            vo.getCnumber() + "",
                            vo.getFactoryName(),
                            parseDate(vo.getDeliveryPeriod()),
                            parseDate(vo.getShipTime()),
                            vo.getTradeTerms()
                    };
                    for (int i = 0; i < array.length; i++) {
                        cell = row.createCell(i + 1);
                        cell.setCellValue(array[i]);
                        //cell.setCellStyle(this.text(workbook));
                    }
                }

            }
        }

        //4、导出下载
        response.setCharacterEncoding("UTF-8");
        response.setHeader("content-disposition","attachment;fileName=export.xlsx");
        ServletOutputStream out = response.getOutputStream();
        workbook.write(out);
        out.close();
    }




    // 日期转字符串的函数
    private String parseDate(Date date){
        if (date == null){
            return null;
        }
        return new SimpleDateFormat("yyyy-MM-dd").format(date);
    }

    private String parseDate(Date date,String pattern){
        if (date == null){
            return null;
        }
        return new SimpleDateFormat(pattern).format(date);
    }


    //大标题的样式
    public CellStyle bigTitle(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("宋体");
        font.setFontHeightInPoints((short)16);
        font.setBold(true);//字体加粗
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);				//横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
        return style;
    }

    //小标题的样式
    public CellStyle title(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("黑体");
        font.setFontHeightInPoints((short)12);
        style.setFont(font);
        style.setAlignment(HorizontalAlignment.CENTER);				//横向居中
        style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
        style.setBorderTop(BorderStyle.THIN);						//上细线
        style.setBorderBottom(BorderStyle.THIN);					//下细线
        style.setBorderLeft(BorderStyle.THIN);						//左细线
        style.setBorderRight(BorderStyle.THIN);						//右细线
        return style;
    }

    //文字样式
    public CellStyle text(Workbook wb){
        CellStyle style = wb.createCellStyle();
        Font font = wb.createFont();
        font.setFontName("Times New Roman");
        font.setFontHeightInPoints((short)10);

        style.setFont(font);

        style.setAlignment(HorizontalAlignment.LEFT);				//横向居左
        style.setVerticalAlignment(VerticalAlignment.CENTER);		//纵向居中
        style.setBorderTop(BorderStyle.THIN);						//上细线
        style.setBorderBottom(BorderStyle.THIN);					//下细线
        style.setBorderLeft(BorderStyle.THIN);						//左细线
        style.setBorderRight(BorderStyle.THIN);						//右细线

        return style;
    }
}
